import json
from email.policy import default

from flask import Flask, request, jsonify, Response
from datetime import datetime

from conn import connect_to_db
from db_utils import GarbageDataHandler
from data_process import calculate_statistics, calculate_street_stats, calculate_community_stats

app = Flask(__name__)
app.config['JSON_AS_ASCII'] = False

# 初始化数据处理器
data_handler = GarbageDataHandler()


@app.route('/api/cleaned-garbage-details', methods=['POST'])
def get_cleaned_garbage_details():
    """获取清洁垃圾详情"""
    data = request.get_json()
    start_time = data.get('startTime')
    end_time = data.get('endTime')

    if not start_time or not end_time:
        return jsonify({
            "code": 400,
            "message": "缺少必要参数: startTime、endTime"
        }), 400

    try:
        datetime.strptime(start_time, '%Y-%m-%d')
        datetime.strptime(end_time, '%Y-%m-%d')
    except ValueError:
        return jsonify({
            "code": 400,
            "message": "日期格式错误，请使用 YYYY-MM-DD 格式"
        }), 400

    try:
        result_data = data_handler.get_cleaned_garbage_data(start_time, end_time)
        statistics = calculate_statistics(result_data)

        return Response(
            json.dumps({
                "code": 200,
                "message": "成功",
                "data": result_data,
                **statistics
            }, ensure_ascii=False),
            content_type='application/json; charset=utf-8'
        )

    except Exception as e:
        return jsonify({
            "code": 500,
            "message": f"数据库错误: {str(e)}"
        }), 500


@app.route('/api/CommunityStreet', methods=['POST'])
def community_street():
    """根据街道/社区查询垃圾清运数据"""
    data = request.get_json()
    start_time = data.get('startTime')
    end_time = data.get('endTime')
    street = data.get('street')
    community_name = data.get('community_name')

    if not start_time or not end_time:
        return jsonify({
            "code": 400,
            "message": "缺少必要参数: startTime、endTime"
        }), 400

    try:
        datetime.strptime(start_time, '%Y-%m-%d %H:%M:%S')
        datetime.strptime(end_time, '%Y-%m-%d %H:%M:%S')
    except ValueError:
        return jsonify({
            "code": 400,
            "message": "时间格式错误，请使用 YYYY-MM-DD HH:MM:SS 格式"
        }), 400


    conditions = [
        "t2.load_time_str BETWEEN %s AND %s",
        "t2.community_type_name = '小区'"
    ]
    params = [start_time, end_time]

    # if street:
    #     conditions.append("t1.street = %s")
    #     params.append(street)
    # elif community_name:
    #     conditions.append("t1.community_name = %s")
    #     params.append(community_name)

    if community_name:
        conditions.append("t1.community_name = %s")
        params.append(community_name)
    elif street:
        conditions.append("t1.street = %s")
        params.append(street)
    else:
        return jsonify({
            "code": 400,
            "message": "必须提供street或community_name参数"
        }), 400

    try:
        result_data = data_handler.get_community_street_data(conditions, params)

        if street:
            statistics = calculate_street_stats(result_data, street)

        if community_name:
            statistics =  calculate_community_stats(result_data, community_name)

        return Response(
            json.dumps({
                "code": 200,
                "message": "成功",
                "data": result_data,
                **statistics
            }, ensure_ascii=False),
            content_type='application/json; charset=utf-8'
        )

    except Exception as e:
        return jsonify({
            "code": 500,
            "message": f"数据库错误: {str(e)}"
        }), 500


@app.route('/api/OldRubbish', methods=['POST'])
def OldRubbish():
    """获取预约垃圾数据"""
    data = request.get_json()
    start_time = data.get('startTime')
    end_time = data.get('endTime')
    street = data.get('street')
    community_name = data.get('community_name')

    # 参数验证
    if not start_time or not end_time:
        return jsonify({
            "code": 400,
            "message": "缺少必要参数: startTime、endTime"
        }), 400

    try:
        datetime.strptime(start_time, '%Y-%m-%d %H:%M:%S')
        datetime.strptime(end_time, '%Y-%m-%d %H:%M:%S')
    except ValueError:
        return jsonify({
            "code": 400,
            "message": "时间格式错误，请使用 YYYY-MM-DD HH:MM:SS 格式"
        }), 400

    # 构建SQL查询条件
    conditions = ["t2.create_time_str BETWEEN %s AND %s"]
    params = [start_time, end_time]

    try:
        conditions.append("t1.street = %s")
        params.append(street)
    # street参数不存在
    except KeyError:
        try:
            conditions.append("t1.community_name = %s")
            params.append(community_name)
        # 两个参数都不存在
        except KeyError:
            return jsonify({
                "code": 400,
                "message": "必须提供street或community_name参数"
            }), 400

    try:
        # 获取数据
        result_data = data_handler.get_old_rubbish_data(conditions, params)

        return Response(
            json.dumps({
                "code": 200,
                "message": "成功",
                "data": result_data
            }, ensure_ascii=False),
            content_type='application/json; charset=utf-8'
        )

    except Exception as e:
        return jsonify({
            "code": 500,
            "message": f"数据库错误: {str(e)}"
        }), 500


@app.route('/api/NewRubbish', methods=['POST'])
def new_rubbish():
    """获取新型垃圾数据"""
    data = request.get_json()
    start_time = data.get('startTime')
    end_time = data.get('endTime')
    street = data.get('street')
    community_name = data.get('community_name')

    # 参数验证
    if not start_time or not end_time:
        return jsonify({
            "code": 400,
            "message": "缺少必要参数: startTime、endTime"
        }), 400

    try:
        datetime.strptime(start_time, '%Y-%m-%d %H:%M:%S')
        datetime.strptime(end_time, '%Y-%m-%d %H:%M:%S')
    except ValueError:
        return jsonify({
            "code": 400,
            "message": "时间格式错误，请使用 YYYY-MM-DD HH:MM:SS 格式"
        }), 400

    # 构建SQL查询条件
    conditions = ["t2.load_time_str BETWEEN %s AND %s"]
    params = [start_time, end_time]

    try:
        # 尝试获取street参数
        conditions.append("t1.street = %s")
        params.append(street)
    # street参数不存在
    except KeyError:
        try:
            conditions.append("t1.community_name = %s")
            params.append(community_name)
        # 两个参数都不存在
        except KeyError:
            return jsonify({
                "code": 400,
                "message": "必须提供street或community_name参数"
            }), 400

    try:
        # 使用数据处理器获取数据
        result_data = data_handler.get_new_rubbish_data(conditions, params)

        return Response(
            json.dumps({
                "code": 200,
                "message": "成功",
                "data": result_data
            }, ensure_ascii=False),
            content_type='application/json; charset=utf-8'
        )

    except Exception as e:
        return jsonify({
            "code": 500,
            "message": f"数据库错误: {str(e)}"
        }), 500

@app.route('/api/FullRubbish', methods=['POST'])
def FullRubbish():
    """获取垃圾满溢事件数据"""
    data = request.get_json()
    start_time = data.get('startTime')
    end_time = data.get('endTime')
    street = data.get('street')
    community_name = data.get('community_name')

    # 参数验证
    if not start_time or not end_time:
        return jsonify({
            "code": 400,
            "message": "缺少必要参数: startTime、endTime"
        }), 400

    try:
        datetime.strptime(start_time, '%Y-%m-%d %H:%M:%S')
        datetime.strptime(end_time, '%Y-%m-%d %H:%M:%S')
    except ValueError:
        return jsonify({
            "code": 400,
            "message": "时间格式错误，请使用 YYYY-MM-DD HH:MM:SS 格式"
        }), 400

    # 构建SQL查询条件
    conditions = ["t2.full_time BETWEEN %s AND %s"]
    params = [start_time, end_time]

    try:
        conditions.append("t1.street = %s")
        params.append(street)
    # street参数不存在
    except KeyError:
        try:
            conditions.append("t1.community_name = %s")
            params.append(community_name)
        # 两个参数都不存在
        except KeyError:
            return jsonify({
                "code": 400,
                "message": "必须提供street或community_name参数"
            }), 400

    try:
        # 使用数据处理器获取数据
        result_data = data_handler.get_full_rubbish_data(conditions, params)

        return Response(
            json.dumps({
                "code": 200,
                "message": "成功",
                "data": result_data
            }, ensure_ascii=False),
            content_type='application/json; charset=utf-8'
        )

    except Exception as e:
        return jsonify({
            "code": 500,
            "message": f"数据库错误: {str(e)}"
        }), 500

@app.route('/api/SmallRubbish', methods=['POST'])
def SmallRubbish():
    """获取小包垃圾事件数据"""
    data = request.get_json()
    start_time = data.get('startTime')
    end_time = data.get('endTime')
    street = data.get('street')
    community_name = data.get('community_name')

    # 参数验证
    if not start_time or not end_time:
        return jsonify({
            "code": 400,
            "message": "缺少必要参数: startTime、endTime"
        }), 400

    try:
        datetime.strptime(start_time, '%Y-%m-%d %H:%M:%S')
        datetime.strptime(end_time, '%Y-%m-%d %H:%M:%S')
    except ValueError:
        return jsonify({
            "code": 400,
            "message": "时间格式错误，请使用 YYYY-MM-DD HH:MM:SS 格式"
        }), 400

    # 构建SQL查询条件
    conditions = ["t2.drop_time BETWEEN %s AND %s"]
    params = [start_time, end_time]


    try:
        conditions.append("t1.street = %s")
        params.append(street)
    # street参数不存在
    except KeyError:
        try:
            conditions.append("t1.community_name = %s")
            params.append(community_name)
        # 两个参数都不存在
        except KeyError:
            return jsonify({
                "code": 400,
                "message": "必须提供street或community_name参数"
            }), 400
    try:
        # 使用数据处理器获取数据
        result_data = data_handler.get_small_rubbish_data(conditions, params)

        return Response(
            json.dumps({
                "code": 200,
                "message": "成功",
                "data": result_data
            }, ensure_ascii=False,default = str),
            content_type='application/json; charset=utf-8'
        )

    except Exception as e:
        return jsonify({
            "code": 500,
            "message": f"数据库错误: {str(e)}"
        }), 500


@app.route('/api/InspectionRectification', methods=['POST'])
def InspectionRectification():
    """获取巡查整改数据"""
    data = request.get_json()
    start_time = data.get('startTime')
    end_time = data.get('endTime')
    street = data.get('street')
    community_name = data.get('community_name')

    # 参数验证
    if not start_time or not end_time:
        return jsonify({
            "code": 400,
            "message": "缺少必要参数: startTime、endTime"
        }), 400

    try:
        datetime.strptime(start_time, '%Y-%m-%d %H:%M:%S')
        datetime.strptime(end_time, '%Y-%m-%d %H:%M:%S')
    except ValueError:
        return jsonify({
            "code": 400,
            "message": "时间格式错误，请使用 YYYY-MM-DD HH:MM:SS 格式"
        }), 400

    # 构建SQL查询条件
    conditions = [
        "xc.type = '居住区'",
        "xc.createtime BETWEEN %s AND %s"
    ]
    params = [start_time, end_time]

    try:
        conditions.append("t1.street = %s")
        params.append(street)
    # street参数不存在
    except KeyError:
        try:
            conditions.append("t1.community_name = %s")
            params.append(community_name)
        # 两个参数都不存在
        except KeyError:
            return jsonify({
                "code": 400,
                "message": "必须提供street或community_name参数"
            }), 400

    try:
        # 使用数据处理器获取数据
        result_data = data_handler.get_inspection_data(conditions, params)

        return Response(
            json.dumps({
                "code": 200,
                "message": "成功",
                "data": result_data
            }, ensure_ascii=False),
            content_type='application/json; charset=utf-8'
        )

    except Exception as e:
        return jsonify({
            "code": 500,
            "message": f"数据库错误: {str(e)}"
        }), 500


if __name__ == '__main__':
    app.run(host='0.0.0.0', port=5000)